/**
 *
 */
package at.ac.tuwien.ifs.calendit.basis.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.jdbc.object.SqlUpdate;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;

import at.ac.tuwien.ifs.calendit.dao.IRaum;
import at.ac.tuwien.ifs.calendit.domain.Raum;
/**
 * @author Pinar
 *
 */
public class JDBCRaumDAO implements IRaum{
        /**
         * Retrieves the logger for this class.
         */
        private static Logger log = Logger.getLogger(JDBCRaumDAO.class);

        /**
         * SQL Datasource. In order to work with data from a database, we need to
         * obtain a connection to the database. A Datasource is part of the JDBC
         * specification and can be seen as a generalized connection factory.
         */
        private DataSource dataSource = null;

        /**
         * Transaction Manager. For encapsulating insert and updates in transaction.
         */
        private PlatformTransactionManager transactionManager = null;
       
        /**
         * SQL Query Strings.
         */
        private String sql_getAllRaum = "";
        private String sql_getMeeting = "";
        //bin mir bei getmeeting nicht sicher ob ich das nicht in Termin impl soll
        private String sql_insertRaum="";
        private String sql_updateRaum = "";
        private String sql_deleteRaum = "";
//      private String sql_getRaumTermin = "";
        private String sql_getRaumByName="";
        private String sql_getInsertRaumId="";
        private String sql_getRaumId="";
               
        /**
         * Query Objects.2
         */
        private Query_GetAllRaum query_getAllRaum;
        private Query_GetMeeting query_getMeeting;
        private Query_InsertRaum query_insertRaum;
        private Query_UpdateRaum query_updateRaum;
        private Query_DeleteRaum query_deleteRaum;
//      private Query_GetRaumTermin query_getRaumTermin;
        private Query_GetRaumByName query_getRaumByName;
       private Query_GetRaumId  query_getRaumId;      
        /** ******************************************************************* */
        /** ******************************************************************* */
        /* INNER CLASSES TO DEFINE SQL STATEMENTS AND MAPPINGS */
        /** ******************************************************************* */
        /** ******************************************************************* */

        /**
         * Defines the base class used for all other queries. This is a private
         * inner class, as this is used only in this data access object
         */
        private class BaseRaumQuery extends MappingSqlQuery {
                /**
                 * The Base query is extended by all other queries
                 *
                 * @param ds SQL Datasource
                 * @see #dataSource
                 * @param sql  The SQL search String
                 */
                public BaseRaumQuery(DataSource ds, String sql) {
                        super(ds, sql);
                }
               
               
                protected Raum mapRow(ResultSet rs, int rowNumber)
                                throws SQLException {
                        Raum raum =new Raum();
                        raum.setRaumID(rs.getInt("raumID"));
                        raum.setRaumname(rs.getString("raumname"));
                        raum.setKapazitaet(rs.getInt("kapazitaet"));
                        raum.setLage(rs.getString("lage"));    
                        return raum;
                       
                }

        }
        /**
         * Retrieves all raum in the DB  This
         * is a private inner class, as this is used only in this data access
         * object.
         */
        private class Query_GetAllRaum extends BaseRaumQuery {
                /**
                 * Fetches all raum from table
                 *
                 * @param ds SQL Datasource
                 *            
                 * @see #dataSource
                 */
                public Query_GetAllRaum(DataSource ds) {
                        super(ds, sql_getAllRaum );
                        compile();
                }
        }

       
        /**
         * Retrieves the aufgabeId of the raum added to database. This is a private
         * inner class, as this is used only in this data access object.
         */
        private class Query_GetMeeting extends BaseRaumQuery {
                /**
                 * Retrieves the aufgabeId of the raum added to database.
                 *
                 * @param ds
                 * SQL Datasource
                 * @see #dataSource
                 */
                public Query_GetMeeting(DataSource ds) {
                        super(ds, sql_getMeeting);
                        compile();
                }

       
        }
//      /**
//       * Retrieves Raum Termine in the DB  This is a
//       * private inner class, as this is used only in this data access object.
//       */
//      private class Query_GetRaumTermin extends BaseRaumQuery {
//              /**
//               * Fetches all raum from table.
//               *
//               * @param ds  SQL Datasource
//               * @see #dataSource
//               */
//              public Query_GetRaumTermin(DataSource ds) {
//                      super(ds, sql_getRaumTermin);
//                      compile();
//              }
//
//      }
        /**
         * Retrieves one raum from the DB. This is a private inner class, as this
         * is used only in this data access object
         */
        private class Query_GetRaumByName extends BaseRaumQuery {
                /**
                 * Fetches only ONE raum from database.
                 *
                 * @param ds  SQL Datasource
                 * @see #dataSource
                 */
                public Query_GetRaumByName(DataSource ds) {
                        super(ds, sql_getRaumByName);
                        super.declareParameter(new SqlParameter("raumname",Types.VARCHAR));
                        compile();
                        //super.declareParameter(new SqlParameter("id", Types.INTEGER));
                        compile();
                       
                }
//                protected Object mapRow(ResultSet rs, int rowNumber)
//                throws SQLException {
//                        // "@p0" is the row name hsqldb creates for the one value returning
//                        // the ID
//                        Integer id = new Integer(rs.getInt("@p0"));
//                        return id;
//                }
        }

        /**
        * Retrieves all Raum in the DB ordered by their ID number. This
        * is a private inner class, as this is used only in this data access
        * object.
        */
        private class Query_GetRaumId extends MappingSqlQuery {
                /**
                * Fetches all Raum from table
                *
                * @param ds SQL Datasource
                *            
                * @see #dataSource
                */
                public Query_GetRaumId(DataSource ds) {
                        super(ds, sql_getRaumId );
                        //super.declareParameter(new SqlParameter("raumID", Types.INTEGER));
//                      super.declareParameter(new SqlParameter("raumname", Types.VARCHAR));
//                      super.declareParameter(new SqlParameter("lage", Types.VARCHAR));
//                      super.declareParameter(new SqlParameter("kapazitaet", Types.INTEGER));
                        compile();
                }
                protected Object mapRow(ResultSet rs, int rowNumber)
          throws SQLException {
                  // "@p0" is the row name hsqldb creates for the one value returning
                  // the ID
                  Integer id = new Integer(rs.getInt("@p0"));
                  return id;
          }
        }
       
        /**
         * Adds one raum to database. This is a private inner class, as this is
         * used only in this data access object.
     * SqlUpdate is a helper class provided by the Spring-Framework
         */
        private class Query_InsertRaum extends SqlUpdate {
                /**
                 * Retrieves the ID of the raum added to database.
                 *
                 * @param ds  SQL Datasource
                 * @see #dataSource
                 */
                public Query_InsertRaum(DataSource ds) {
                        setDataSource(ds);
                        setSql(sql_insertRaum);
                        declareParameter(new SqlParameter("raumID", Types.INTEGER));
                        declareParameter(new SqlParameter("raumname", Types.VARCHAR));
                        declareParameter(new SqlParameter("lage", Types.VARCHAR));
                        declareParameter(new SqlParameter("kapazitaet", Types.INTEGER));
                        log.info("Query_InsertRaum");
                }
        }
               
        /**
         * Updates one raum in the database. This is a private inner class, as
         * this is used only in this data access object.
     * SqlUpdate is a helper class provided by the Spring-Framework
         */
        private class Query_UpdateRaum extends SqlUpdate {
                /**
                 * Create and initialises the SQL-Query for updating a raum.
                 *
                 * @param ds  SQL Datasource
                 * @see #dataSource
                 */
                public Query_UpdateRaum(DataSource ds) {
                        setDataSource(ds);
                        setSql(sql_updateRaum);
               
                        declareParameter(new SqlParameter("raumID", Types.INTEGER));
                        declareParameter(new SqlParameter("raumname", Types.VARCHAR));
                        declareParameter(new SqlParameter("lage", Types.VARCHAR));
                        declareParameter(new SqlParameter("kapazitaet", Types.INTEGER));
                }
        }
       
        /**
         * Deletes one raum from the database. This is a private inner class, as
         * this is used only in this data access object.
         */
        public class Query_DeleteRaum extends SqlUpdate {
                /**
                 * Creates and initializes the SQL-Query for deleting a raum.
                 *
                 * @param ds  SQL Datasource
                 * @see #dataSource
                 */
                public Query_DeleteRaum(DataSource ds) {
                        setDataSource(ds);
                        setSql(sql_deleteRaum);
                        declareParameter(new SqlParameter("raumID", Types.INTEGER));
                }
       
        }



                /** ******************************************************************* */
                /** ******************************************************************* */
                /* C O N S T R U C T O R */
                /** ******************************************************************* */
                /** ******************************************************************* */

                public JDBCRaumDAO() {
                        super();
                }
                /**
                 * The Initialise Method must be called after all bean values are set,
                 * particularly the datasource and the transaction manager. This is actually
                 * performed by the Spring Framework, which sets first of all, all Java Bean
                 * Properties and eventually calls this init method (see bean definition in
                 * beans.xml configuration file)
                 */
                public void init() {
                        log.info("Initialise RaumDAO");

                        query_getAllRaum = new Query_GetAllRaum(dataSource);
                        query_insertRaum = new Query_InsertRaum(dataSource);
                        query_updateRaum = new Query_UpdateRaum(dataSource);
                        query_deleteRaum = new Query_DeleteRaum(dataSource);
                        query_getMeeting = new Query_GetMeeting(dataSource);
//                      query_getRaumTermin =new Query_GetRaumTermin(dataSource);
                        query_getRaumByName = new Query_GetRaumByName(dataSource);
                        query_getRaumId = new Query_GetRaumId(dataSource);
               
                }
               
                /**
                 * The Destroy Method is called by the Spring Framework to end the lifecycle
                 * of this bean, but <b>only</b> when the bean is created as singleton.
                 * Check the bean definition in beans.xml configuration file for details.
                 */
                public void destroy() {
                        log.info("Destroy RaumDAO");
                }
               
                /** ******************************************************************* */
                /** ******************************************************************* */
                /*
                 * BEAN SETTERS FOR DEPENDENCY INJECTION
                 *
                 * Dependency Injection is a design pattern in which the responsibility for
                 * object creation and object linking is removed from the objects themselves
                 * and transferred to a factory object. It is a way to achieve loose
                 * coupling between objects and results in highly testable objects
                 * (controlled unit tests).
                 *
                 * Factory Object: (Design Pattern) is an object for creating other objects.
                 * Typically a factory has a method for every kind of object it is capable
                 * of creating. these methods optionally accept parameters defining how the
                 * object is created and then return the created object.
                 */
                /** ******************************************************************* */
                /** ******************************************************************* */

                /**
                 * Sets the SQL String to get all raum.
                 *
                 * @param sql_getPrivat
                 *            SQL Statement as String
                 */
                public void setSql_getAllRaum(String sql_getAllRaum) {
                        this.sql_getAllRaum = sql_getAllRaum;
                }
               
               
                /**
                 * Sets the SQL String to insert one raum into the database.
                 *
                 * @param sql_insertRaum
                 *            SQL Statement as String
                 */
                public void setSql_insertRaum(String sql_insertRaum) {
                        this.sql_insertRaum = sql_insertRaum;
                }
                /**
                * Sets the SQL String to retrieve the ID of the last executed SQL
                * Statement.
                *
                * @param sql_getInsertRaumId
                *            SQL Statement as String
                */
                public void setSql_getInsertRaumId(String sql_getInsertRaumId) {
                        this.sql_getInsertRaumId = sql_getInsertRaumId;
                }
               
       
                       
                public void setSql_getMeeting(String sql_getMeeting) {
                        this.sql_getMeeting= sql_getMeeting;
                }      
                /**
                 * Sets the SQL String to update a raum.
                 *
                 * @param sql_updateraum
                 *            SQL Statement as String
                 */
                public void setSql_updateRaum(String sql_updateRaum) {
                        this.sql_updateRaum = sql_updateRaum;
                }
               
                /**
                 * Sets the SQL String to delete a raum.
                 *
                 * @param sql_deleteraum
                 *            SQL Statement as String
                 */
                public void setSql_deleteRaum(String sql_deleteRaum) {
                        this.sql_deleteRaum = sql_deleteRaum;
                }
                public void setSql_getRaumByName(String sql_getRaumByName) {
                        this.sql_getRaumByName = sql_getRaumByName;
                        log.info("setSql_getRaumByName");
                }
                public void setSql_getRaumId(String sql_getRaumId) {
                    this.sql_getRaumId = sql_getRaumId;
                    log.info("setSql_getRaumId");
            }
//              public void setQuery_getAllRaum(Query_GetAllRaum query_getAllRaum) {
//                      this.query_getAllRaum = query_getAllRaum;
//              }
        /**
         * @param query_getMeeting the query_getMeeting to set
         */
        public void setQuery_getMeeting(Query_GetMeeting query_getMeeting) {
                        this.query_getMeeting = query_getMeeting;
               
        }
       
        /**
         * @param query_updateRaum the query_updateRaum to set
         */
        public void setQuery_updateRaum(Query_UpdateRaum query_updateRaum) {
                this.query_updateRaum = query_updateRaum;
        }
        /**
         * Sets the Datasource to connect to database.
         *
         * @param dataSource
         * SQL Datasource
         * @see #dataSource
         */
        public void setDataSource(DataSource dataSource) {
                this.dataSource = dataSource;
        }

        /**
         * Sets the transaction manager.
         *
         * @param transactionManager
         * central interface in Spring's transaction infrastructure
         * @see #transactionManager
         */
        public void setTransactionManager(
                        PlatformTransactionManager transactionManager) {
                this.transactionManager = transactionManager;
        }
//      public Query_GetAllRaum getQuery_getAllRaum() {
//              return query_getAllRaum;
//      }
//      
//      
//      /**
//       * @return the query_getMeeting
//       */
//      public Query_GetMeeting getQuery_getMeeting() {
//              return query_getMeeting;
//      }
//      
//      /**
//       * @return the query_updateRaum
//       */
//      public Query_UpdateRaum getQuery_updateRaum() {
//              return query_updateRaum;
//      }

       


//      /**
//       * @param query_getRaumTermin the query_getRaumTermin to set
//       */
//      public void setQuery_getRaumTermin(Query_GetRaumTermin query_getRaumTermin) {
//              this.query_getRaumTermin = query_getRaumTermin;
//      }
//      /**
//       * @return the query_getRaumTermin
//       */
//
//      public Query_GetRaumTermin getQuery_getRaumTermin() {
//              return query_getRaumTermin;
//      }

        /** ******************************************************************* */
        /** ******************************************************************* */
        /*
         * DAO METHODS
         *
         * A Data Access Object (DAO) is a component which provides a common
         * interface between the application and one or more data storage devices,
         * such as a database or a file. The advantage of using data access objects
         * is that any business object (which contains application or operation
         * specific details) does not require direct knowledge of the final
         * destination for the information it manipulates. As a result, _if_ it is
         * necessary to change where or how the data is stored, these modifications
         * can be made without needing to change the main application.
         */
        /** ******************************************************************* */
        /** ******************************************************************* */




        public Raum add(Raum raum) {
                log.info("Add Raum  = " + raum.getRaumname()+ " "+raum.getLage()+" "
                        +raum.getKapazitaet());
        log.info("Initialise SQL Parameters");
        final Object[] param = new Object[] {null,raum.getRaumname(),raum.getLage(),raum.getKapazitaet() };
        log.debug("Initialise Transaction Manager");
        TransactionTemplate tt = new TransactionTemplate(transactionManager);
        Object result = tt.execute(new TransactionCallback() {
                        public Object doInTransaction(TransactionStatus status) {
                                // The transaction is run from here
                                log.info("Start Transaction");
                                query_insertRaum.update(param);
                                log.info("PARAM");
                               
                                /*
                                * activate the following error line to create an Error which
                                * terminates this method. One will see, that the complete
                                * transaction is rolled back, hence the insert statement above
                                * is not executed, alternatively the second rollback statement
                                * can be activated with the same result which in that case is a
//                              * manual rollback of the transaction
//                              */
//                              
//                              
//                              * result from query is a list, actually containing only one row
//                              * and one column
//                              */
                                List results = query_getRaumId.execute();
                                log.info("List");
                                Integer id = (Integer) results.get(0);
                                log.info("End Transaction");
                                return id;
                                /*
                                * and the transaction ends here! if no error occurs the
                                * transaction is committed by Spring otherwise it is rolled
                                * back
                                */
                        }
        });
        Integer id = (Integer) result;
        raum.setRaumID(id);
        log.info("Return ID from inserted dataset = " + id);
        return (id!=0) ? raum : null;
        }
       
        public boolean delete(int id) {
                log.info("Delete Raum ID = " + id);
                log.debug("Initialize SQL Parameters");
                final Object[] param = new Object[] { id };
                log.debug("Executing SQL");
                if (query_deleteRaum.update(param) == 1) {
                        log.debug("Deleting successfull");
                        return true;
                }
                log.error("Deleting Raum ID = " + id + " failed");
                return false;
        }
//      public Raum getRaumTermin(String bez){
//              List <Raum> rm = query_getRaumTermin.execute();
//              if (rm.size() == 1) {
//                      Raum r = rm.get(0);
//                      log.debug("Getting RaumTermin "+rm+" successfull");
//                      return r;
//              } else {
//                      log.error("No Raum "+rm+" found!");
//                      return null;
//              }
//      }
       

        @SuppressWarnings("unchecked")
        public List<Raum> getAll() {
                log.info("Get all Raum");
                List<Raum> rm = null;
                rm = query_getAllRaum.execute();
                log.debug("Raum List contains " + rm.size() + " RAum");
                return rm;
       
        }
       
        public boolean deleteAll() {
                // TODO Auto-generated method stub
                return false;
        }

       
        public Raum getRaumByName(String raumname) {
                Object param[] = new String[]{raumname};
                        List<Raum> raum = query_getRaumByName.execute(param);
                        if (raum.size() != 0){
                                log.info("getting raum by name successfull");
                                return raum.get(0);
                        } else
                        return null;
        }
        public Raum update(Raum raum) {
                return updateRaum(raum, raum.getRaumID());
        }
       
        public Raum updateRaum(Raum raum, long id) {
                log.info("Update Raum, ID = " + raum.getRaumID() + " new ID = "
                        + id);
                log.info("Initialise SQL Parameters");
                final Object[] param = new Object[] {raum.getRaumname(),raum.getLage(),raum.getKapazitaet(),id};
                log.info("Execute Update");
                if (query_updateRaum.update(param) == 1) {
                        log.info("Update Successfull");
                        return raum;
                }
                log.error("Update for Raum ID = " + id + " failed.");
                return null;
        }
        public Raum getRaumId(int id) {
                 log.info("Get Raum Id="+id);
             List  raum = query_getRaumId.execute();
             if (raum.size() == 1) {
                     Raum r = (Raum) raum.get(0);
                     log.info("Returning Raum \"" + r.getRaumID()+ " "+ r.getRaumname()+ " "
                                     + r.getLage()+ " "+ r.getKapazitaet()+ " "+ "\"");
                     return r;
             } else {
                     log.info("No Raum data");
                     return null;
             }
        }
                public Raum getById(int id) {
                        // TODO Auto-generated method stub
                        return null;
                }
               
               
}
